{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from tqdm import tqdm\n",
    "import csv\n",
    "import pickle\n",
    "import operator\n",
    "import gc\n",
    "from joblib import Parallel, delayed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
<<<<<<< HEAD:modelB/0-correct_test_onBoardDate/0-1.CullDataWithError.ipynb
    "origin_train_data_path = '../../data/train0711.csv'\n",
    "port_info_path = '../../data/DataForModelB/port_info_dict_dump.file'\n",
    "\n",
    "carrierNameSet_info_path = '../../data/DataForModelB/data_for_correct_test_onBoardDate/carrierNameSet_dump.file'\n",
    "washed_train_order_brief_path = '../../data/DataForModelB/data_for_correct_test_onBoardDate/washed_train_order_brief.csv'\n",
    "train_data_by_order_path_folder = '../../data/DataForModelB/data_for_correct_test_onBoardDate/train_data_by_order'"
=======
    "# origin_train_data_path = '../data/train0523.csv'\n",
    "origin_train_data_path = '../data/train_spilit.csv'\n",
    "port_info_path = '../data/DataForModelB/port_info_dict_dump.file'\n",
    "\n",
    "carrierNameSet_info_path = '../data/DataForModelB/data_for_correct_test_onBoardDate/carrierNameSet_dump.file'\n",
    "washed_train_order_brief_path = '../data/DataForModelB/data_for_correct_test_onBoardDate/washed_train_order_brief.csv'\n",
    "train_data_by_order_path_folder = '../data/DataForModelB/data_for_correct_test_onBoardDate/train_data_by_order'"
>>>>>>> 081c522bdcef1cb40c539a5a14ec6d26a3b53059:modelB/0-correct_test_onBoardDate/0-1.CullDataWithError.ipynb
   ]
  },
  {
   "cell_type": "code",
<<<<<<< HEAD:modelB/0-correct_test_onBoardDate/0-1.CullDataWithError.ipynb
   "execution_count": 9,
=======
   "execution_count": 3,
>>>>>>> 081c522bdcef1cb40c539a5a14ec6d26a3b53059:modelB/0-correct_test_onBoardDate/0-1.CullDataWithError.ipynb
   "metadata": {},
   "outputs": [],
   "source": [
    "# load data\n",
    "with open(port_info_path, \"rb\") as f:\n",
    "    port_data = pickle.load(f)\n",
    "\n",
    "carrierName_set = pd.read_csv(origin_train_data_path, usecols = [1], header=None, names=['carrierName'])['carrierName'].unique()\n",
    "with open(carrierNameSet_info_path, \"wb\") as f:\n",
    "    pickle.dump(carrierName_set, f)"
   ]
  },
  {
   "cell_type": "code",
<<<<<<< HEAD:modelB/0-correct_test_onBoardDate/0-1.CullDataWithError.ipynb
   "execution_count": 10,
=======
   "execution_count": 4,
>>>>>>> 081c522bdcef1cb40c539a5a14ec6d26a3b53059:modelB/0-correct_test_onBoardDate/0-1.CullDataWithError.ipynb
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
<<<<<<< HEAD:modelB/0-correct_test_onBoardDate/0-1.CullDataWithError.ipynb
      "76it [13:50, 10.92s/it]\n"
=======
      "3it [00:24,  8.25s/it]\n"
>>>>>>> 081c522bdcef1cb40c539a5a14ec6d26a3b53059:modelB/0-correct_test_onBoardDate/0-1.CullDataWithError.ipynb
     ]
    },
    {
     "data": {
      "text/plain": [
<<<<<<< HEAD:modelB/0-correct_test_onBoardDate/0-1.CullDataWithError.ipynb
       "41"
      ]
     },
     "execution_count": 10,
=======
       "58"
      ]
     },
     "execution_count": 4,
>>>>>>> 081c522bdcef1cb40c539a5a14ec6d26a3b53059:modelB/0-correct_test_onBoardDate/0-1.CullDataWithError.ipynb
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "order_list = []\n",
    "\n",
    "# 给每个订单单独的写指针，每个订单的数据写到不同文件\n",
    "csv_file = {}\n",
    "csv_writer = {}\n",
    "\n",
    "train_data_origin_chunk = pd.read_csv(origin_train_data_path, chunksize = 2000000, usecols = [0,1,2,3,4,6,7,12], header=None\n",
    "                                          , names=['loadingOrder', 'carrierName','timestamp','longitude','latitude','speed','direction','TRANSPORT_TRACE'])    \n",
    "\n",
    "for train_data_origin in tqdm(train_data_origin_chunk):\n",
    "    # if speed < 0 or TRANSPORT_TRACE if Nan, drop it!\n",
    "    train_data_origin = train_data_origin[train_data_origin['speed'] > 0]\n",
    "    train_data_origin = train_data_origin[pd.notnull(train_data_origin['TRANSPORT_TRACE'])]\n",
    "    \n",
    "    valid_orders = train_data_origin['loadingOrder'].unique()\n",
    "    for order in valid_orders:\n",
    "        if not order in order_list:\n",
    "            order_list.append(order)\n",
    "            order_writer_path = os.path.join(train_data_by_order_path_folder, \"{}_origin_data.csv\".format(order))\n",
    "            csv_file[order] = open(order_writer_path,'w',encoding='utf-8',newline='')\n",
    "            csv_writer[order] = csv.writer(csv_file[order])            \n",
    "    \n",
    "    # 将货运公司编号为ID\n",
    "    carrierName_set = train_data_origin['carrierName'].unique()\n",
    "    for index ,carrierName in enumerate(carrierName_set, 1):\n",
    "        train_data_origin.loc[(train_data_origin.carrierName == carrierName), 'carrierName'] = index\n",
    "    \n",
    "    # 输出不同订单的数据\n",
    "    for row in train_data_origin.itertuples():\n",
    "        order = row.loadingOrder\n",
    "        row_info = row._asdict()\n",
    "        row_info.pop('Index')\n",
    "        csv_writer[order].writerow(row_info.values())\n",
    "\n",
    "for k,v in csv_file.items():\n",
    "    v.close()\n",
    "\n",
    "gc.collect()"
   ]
  },
  {
   "cell_type": "code",
<<<<<<< HEAD:modelB/0-correct_test_onBoardDate/0-1.CullDataWithError.ipynb
   "execution_count": 11,
=======
   "execution_count": 8,
>>>>>>> 081c522bdcef1cb40c539a5a14ec6d26a3b53059:modelB/0-correct_test_onBoardDate/0-1.CullDataWithError.ipynb
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
<<<<<<< HEAD:modelB/0-correct_test_onBoardDate/0-1.CullDataWithError.ipynb
      "100%|██████████| 13136/13136 [34:15<00:00,  6.39it/s] \n"
=======
      "100%|██████████| 590/590 [00:37<00:00, 15.69it/s]\n"
>>>>>>> 081c522bdcef1cb40c539a5a14ec6d26a3b53059:modelB/0-correct_test_onBoardDate/0-1.CullDataWithError.ipynb
     ]
    }
   ],
   "source": [
    "# 单个 order 的处理函数\n",
    "def _handle_single_order_for_start_info_and_arrive_info(o_index, order):\n",
    "    # 获取当前订单的数据并且重新编码订单号（缩小文件体积）\n",
    "    order_writer_path = os.path.join(train_data_by_order_path_folder, \"{}_origin_data.csv\".format(order))\n",
    "    order_data = pd.read_csv(order_writer_path, header=None,\n",
    "                            names=['loadingOrder', 'carrierName','timestamp','longitude','latitude','speed','direction','TRANSPORT_TRACE'])\n",
    "    order_data['loadingOrder'] = o_index+1\n",
    "    # 获取承运商、船只ID、路由\n",
    "    order_ID = order_data.loc[0, 'loadingOrder']\n",
    "    carrierName = order_data.loc[0, 'carrierName']\n",
    "    TRANSPORT_TRACE = order_data.loc[0, 'TRANSPORT_TRACE']\n",
    "    \n",
    "    # 获取运单路径与起航港口以及起航港口、目的地港口的经纬度信息\n",
    "    if not type(TRANSPORT_TRACE) == type('str'):\n",
    "        return None\n",
    "    ports = TRANSPORT_TRACE.split(\"-\")\n",
    "    start_port, dest_port = ports[0], ports[-1]\n",
    "    if (not start_port in port_data) or (not dest_port in port_data) or (len(ports) < 2):\n",
    "        return None\n",
    "    start_port_lon, start_port_lat = port_data[start_port]['LON'], port_data[start_port]['LAT']\n",
    "    dest_port_lon, dest_port_lat = port_data[dest_port]['LON'], port_data[dest_port]['LAT']\n",
    "    \n",
    "    # 获取起航时间\n",
    "    start_time = order_data['timestamp'].min()\n",
    "    start_index = 0\n",
    "    for (index, info_item) in order_data.iterrows():\n",
    "        if abs(info_item['longitude']-start_port_lon) < 0.3 and abs(info_item['latitude']-start_port_lat) < 0.3 and info_item['speed'] > 0:\n",
    "            start_time = max(start_time, info_item['timestamp'])\n",
    "            start_index = index\n",
    "            break \n",
    "    \n",
    "    # 获取到达目的地时间，这里需要用 GPS 判断\n",
    "    end_time = order_data['timestamp'].max()\n",
    "    end_index = order_data.size-1\n",
    "    for (index, info_item) in order_data.iterrows():\n",
    "        if abs(info_item['longitude'] - dest_port_lon) < 0.3 and abs(info_item['latitude'] - dest_port_lat) < 0.3:\n",
    "            end_time = min(end_time, info_item['timestamp'])\n",
    "            end_index = index\n",
    "            break\n",
    "    if (end_index - start_index < 20):\n",
    "        return None\n",
    "    \n",
    "    # 截取启航到到港段的数据\n",
    "    order_data = order_data[start_index:end_index+1].reset_index(drop=True)\n",
    "\n",
    "    order_data_path = os.path.join(train_data_by_order_path_folder, \"{}_gps_data.csv\".format(order_ID))\n",
    "    order_data.to_csv(order_data_path, header=False, index=False,\n",
    "                      columns=['timestamp', 'longitude', 'latitude', 'speed'])\n",
    "    return order_ID, carrierName, TRANSPORT_TRACE, start_time, end_time\n",
    "\n",
    "\n",
    "# get each order's info and write to file\n",
    "train_data = Parallel(n_jobs=8)(delayed(_handle_single_order_for_start_info_and_arrive_info)\n",
    "                                (index, order)\n",
    "                                for index, order in enumerate(tqdm(order_list)))\n",
    "# for index, order in enumerate(tqdm(order_list)):\n",
    "#     _handle_single_order_for_start_info_and_arrive_info(index, order)\n",
    "#     break"
   ]
  },
  {
   "cell_type": "code",
<<<<<<< HEAD:modelB/0-correct_test_onBoardDate/0-1.CullDataWithError.ipynb
   "execution_count": 12,
=======
   "execution_count": 9,
>>>>>>> 081c522bdcef1cb40c539a5a14ec6d26a3b53059:modelB/0-correct_test_onBoardDate/0-1.CullDataWithError.ipynb
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
<<<<<<< HEAD:modelB/0-correct_test_onBoardDate/0-1.CullDataWithError.ipynb
      "100%|██████████| 13136/13136 [00:00<00:00, 341723.23it/s]\n"
=======
      "100%|██████████| 590/590 [00:00<00:00, 115815.95it/s]\n"
>>>>>>> 081c522bdcef1cb40c539a5a14ec6d26a3b53059:modelB/0-correct_test_onBoardDate/0-1.CullDataWithError.ipynb
     ]
    }
   ],
   "source": [
    "# order_brief 的写指针\n",
    "order_brief_csvfile = open(washed_train_order_brief_path, 'w', newline='')\n",
    "order_brief_writer = csv.writer(order_brief_csvfile)\n",
    "order_brief_writer.writerow(['loadingOrder', 'carrierName', 'TRANSPORT_TRACE', 'onboardDate', 'ETA'])\n",
    "\n",
    "# 写 washed_train_order_brief 与 washed_train_order_gps\n",
    "for item in tqdm(train_data):\n",
    "    if not item:\n",
    "        continue\n",
    "    order_brief_writer.writerow(item[0:5])\n",
    "\n",
    "order_brief_csvfile.close()\n",
    "\n",
    "# 清理原始文件\n",
    "for order in order_list:\n",
    "    order_writer_path = os.path.join(train_data_by_order_path_folder, \"{}_origin_data.csv\".format(order))\n",
    "    os.remove(order_writer_path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Conda-python3",
   "language": "python",
   "name": "conda-python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
